package com.hnl.test;

import java.io.FileInputStream;
import java.io.InputStream;
import java.text.ParseException;
import java.util.Date;
import java.util.Iterator;

import javax.servlet.http.HttpServlet;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.hnl.core.HNLPageDispatch;
import com.hnl.core.aware.HNLAuthAware;
import com.hnl.core.aware.HNLTalentAware;
import com.hnl.core.aware.MiscTypeAware;
import com.hnl.dao.ActivityDAO;
import com.hnl.dao.DAOException;
import com.hnl.dao.EducationDAO;
import com.hnl.dao.HNLDAOFactory;
import com.hnl.dao.MiscDAO;
import com.hnl.dao.TalentDAO;
import com.hnl.to.Activity;
import com.hnl.to.Education;
import com.hnl.to.Misc;
import com.hnl.to.Talent;
import com.hnl.util.FormatUtil;

@SuppressWarnings("serial")
public class ImportData extends HttpServlet implements HNLAuthAware,
		HNLTalentAware, MiscTypeAware, HNLPageDispatch {
	public static final Logger LOG = LoggerFactory.getLogger(ImportData.class);
	private static Integer IMPORTUSER = 1;
	private static Date NOWDATE = new Date();

	private Misc misc = new Misc();
	private Talent talent = new Talent();
	private Activity act = new Activity();
	private Education newEdu = new Education();

	public void main() {
		try {
			//String filePath = "C:/HNLTalentImport_20110721.xls";
			String filePath = "D:/HNLTalentImport_20110721.xls";
			InputStream inp = new FileInputStream(filePath);
			Workbook wb = WorkbookFactory.create(inp);
			Sheet sheet = wb.getSheetAt(1);
			int k = 0, nextIF = 0, nowIF = 0, cellNum = 0;
			Integer talentID = 0, actID=0, eduID=0;
			String[] talentInfo = new String[29];
			String[] activityInfo = new String[2];
			String[] educationInfo = new String[4];
			HSSFCell hssfCell;
			boolean actIsEmpty = true;
			boolean eduIsEmpty = true;
			String logInfo="";

			for (Iterator<Row> iter = sheet.iterator(); iter.hasNext();) {
				nextIF = 0;
				nowIF = 0;
				cellNum = 0;
				Row nowrow = iter.next();
				// row 1 is column group, so don't parser
				if (nowrow.getRowNum() < 2)
					continue;

				if (nowrow.getRowNum() >= 2) { // real contents
					// add Talent
					k = 0;
					nextIF = talentInfo.length;
					for (cellNum = 0; cellNum < nextIF; cellNum++) {
						hssfCell = (HSSFCell) nowrow.getCell(cellNum);
						if (hssfCell == null) {
							talentInfo[k] = "";
						} else {
							talentInfo[k] = convertToString(hssfCell);
						}						
						k++;
					}
					talentID = saveTalentInfo(talentInfo);
					logInfo="talentID = " + talentID;
					System.out.print("talentID = " + talentID);
					

					// add Activity
					k = 0;
					nowIF = nextIF;
					nextIF = nextIF + activityInfo.length;
					for (cellNum = nowIF; cellNum < nextIF; cellNum++) {
						hssfCell = (HSSFCell) nowrow.getCell(cellNum);
						if (hssfCell == null) {
							activityInfo[k] = "";
						} else {
							activityInfo[k] = convertToString(hssfCell);
						}
						if ((k==1)&&(activityInfo[k].trim().length() != 0)){
							actIsEmpty = false;
						}
						k++;
					}
					
					if (!actIsEmpty){
						actID=saveActivityInfo(talentID, activityInfo);
					}
					System.out.print(", actID = " + actID);
					logInfo+=", actID = " + actID;
					//reset value
					actIsEmpty = true;

					// add Education
					k = 0;
					nowIF = nextIF;
					nextIF = nextIF + educationInfo.length;
					for (cellNum = nowIF; cellNum < nextIF; cellNum++) {
						hssfCell = (HSSFCell) nowrow.getCell(cellNum);
						if (hssfCell == null) {
							educationInfo[k] = "";
						} else {
							educationInfo[k] = convertToString(hssfCell);
						}
						
						if (educationInfo[k].trim().length() != 0){
							eduIsEmpty = false;
						}

						k++;
					}
					if (!eduIsEmpty){
						eduID = saveEducationInfo(talentID, educationInfo);
					}
					System.gc();
					System.out.print(", eduID = " + eduID);					
					logInfo+=", eduID = " + eduID;
					
					//reset value
					eduIsEmpty = true;
					
					System.out.print("\n");
					logInfo+="\n" ;
					LOG.info(logInfo);
				}// END else i>1
			}// END for(int i=0; i<totalrow; i++){
		} catch (Exception e) {
			LOG.error("Error during ImportData.", e);
		}
	}

	private Integer saveTalentInfo(String[] talentInfo) {
		Integer talentID = 0;

		try {
			TalentDAO talentDAO = HNLDAOFactory.getDAOFactory(
					HNLDAOFactory.HNLDAOFACTORY).getTalentDAO();
			MiscDAO miscDAO = HNLDAOFactory.getDAOFactory(
					HNLDAOFactory.HNLDAOFACTORY).getMiscDAO();

			// test add talent
			talent.seteFirstname(talentInfo[0].toString());
			talent.seteLastname(talentInfo[1].toString());
			talent.setcFirstname(talentInfo[2].toString());
			talent.setcLastname(talentInfo[3].toString());
			if(talentInfo[4].toString().trim().length()!= 0){
				talent.setBirthdate(FormatUtil.getDateFormatUS().parse(
						talentInfo[4].toString()));
			}else{
				talent.setBirthdate(null);
			}
			if(talentInfo[5].toString().trim().length()!= 0){
				misc = miscDAO.loadMiscByMiscNameAndType(talentInfo[5].toString(),
						"NATIONALITY");
				talent.setNationality(Integer.valueOf(misc.getMiscID()));
			}else{
				talent.setNationality(null);
			}
			if(talentInfo[6].toString().trim().length()!= 0){
				misc = miscDAO.loadMiscByMiscNameAndType(talentInfo[6].toString(),
						"GENDER");
				talent.setGender(Integer.valueOf(misc.getMiscID()));
			}else{
				talent.setGender(null);
			}
			if(talentInfo[7].toString().trim().length()!= 0){
				misc = miscDAO.loadMiscByMiscNameAndType(talentInfo[7].toString(),
						"MARITAL_STATUS");
				talent.setMaritalStatus(Integer.valueOf(misc.getMiscID()));
			}else{
				talent.setMaritalStatus(null);
			}
			talent.setOfficeTel(talentInfo[8].toString());
			talent.setHomePhone(talentInfo[9].toString());
			talent.setCell1(talentInfo[10].toString());
			talent.setCell2(talentInfo[11].toString());
			talent.setComMail(talentInfo[12].toString());
			talent.setAdditionalReferenceInfo(talentInfo[13].toString());
			talent.setAddress(talentInfo[14].toString());
			talent.setOthers(talentInfo[15].toString());
			talent.setCompany(talentInfo[16].toString());
			if(talentInfo[17].toString().trim().length()!= 0){
				misc = miscDAO.loadMiscByMiscNameAndType(talentInfo[17].toString(),
						"INDUSTRY_LEVEL_1");
				talent.setIndustry1(misc.getMiscID());
			}else{
				talent.setIndustry1(null);
			}
			if(talentInfo[18].toString().trim().length()!= 0){
				misc = miscDAO.loadMiscByMiscNameAndType(talentInfo[18].toString(),
						"CURRENT_COUNTRY");
				talent.setCountry(misc.getMiscID());
			}else{
				talent.setCountry(null);
			}
			talent.setCity(talentInfo[19].toString());
			talent.setPosition(talentInfo[20].toString());
			talent.setJobfunction(talentInfo[21].toString());
			talent.setSalary(talentInfo[22].toString());
			talent.setExpertise(talentInfo[23].toString());
			talent.setLanguageSkill(talentInfo[24].toString());
			talent.setTotalYearsOfWork(talentInfo[25].toString());
			talent.setWorkExperience(talentInfo[26].toString());
			talent.setCareerGoal(talentInfo[27].toString());
			talent.setComment(talentInfo[28].toString());

			talent.setIsdelete(false);
			talent.setCreateUser(IMPORTUSER);
			talent.setCreateDate(NOWDATE);
			talent.setModifyUser(IMPORTUSER);
			talent.setModifyDate(NOWDATE);

			Talent newTalent=talentDAO.saveTalent(talent);

			talentID = newTalent.getTalentId();

		} catch (DAOException e) {
			LOG.error("Error to add talent.", e);
		} catch (ParseException e) {
			LOG.error("Error to add talent.", e);
		}
		return talentID;
	}

	private Integer saveActivityInfo(Integer talentID, String[] activityInfo) {
		Integer returnValue = 0;
		try {
			ActivityDAO activityDAO = HNLDAOFactory.getDAOFactory(
					HNLDAOFactory.HNLDAOFACTORY).getActivityDAO();
			MiscDAO miscDAO = HNLDAOFactory.getDAOFactory(
					HNLDAOFactory.HNLDAOFACTORY).getMiscDAO();

			act.setTalentID(talentID);
			if(activityInfo[0].toString().trim().length()!= 0){
				misc = miscDAO.loadMiscByMiscNameAndType(
						activityInfo[0].toString(), "ACTIVITY_TYPE");
				if(misc != null){
					act.setActivityType(Integer.valueOf(misc.getMiscID()));
				}else{
					misc = miscDAO.loadMiscByMiscNameAndType(
							"Phone screen", "ACTIVITY_TYPE");
					act.setActivityType(Integer.valueOf(misc.getMiscID()));
				}
			}else{
				misc = miscDAO.loadMiscByMiscNameAndType(
						"Phone screen", "ACTIVITY_TYPE");
				act.setActivityType(Integer.valueOf(misc.getMiscID()));
			}
			act.setDescription(activityInfo[1].toString());
			act.setIsdelete(false);
			act.setCreateDate(NOWDATE);
			act.setCreateUser(IMPORTUSER);
			act.setModifyDate(NOWDATE);
			act.setModifyUser(IMPORTUSER);
			act.setIsdelete(false);
			Activity newAct=activityDAO.saveActivity(act);
			
			returnValue = newAct.getActivityID();

		} catch (DAOException e) {
			LOG.error("Error to add talent.", e);
		}
		return returnValue;
	}

	private Integer saveEducationInfo(Integer talentID, String[] referrerInfo) {
		Integer returnValue = 0;
		try {
			EducationDAO eduDAO = HNLDAOFactory.getDAOFactory(
					HNLDAOFactory.HNLDAOFACTORY).getEducationDAO();

			newEdu.setTalent_ID(talentID);
			newEdu.setDegree(referrerInfo[0]);
			newEdu.setSchool(referrerInfo[1]);
			newEdu.setMajor(referrerInfo[2]);
			newEdu.setDuration(referrerInfo[3]);
			newEdu.setIsdelete(false);

			Education newEducation=eduDAO.saveEducation(newEdu);
			returnValue = newEducation.getTalent_education_ID();
		} catch (DAOException e) {
			LOG.error("Error to add talent.", e);
		}
		
		return returnValue;
	}

	private String convertToString(HSSFCell cell) {
		String ret = "";

		if (cell != null) {
			int type = cell.getCellType();
			switch (type) {
			case HSSFCell.CELL_TYPE_BLANK:
			case HSSFCell.CELL_TYPE_BOOLEAN:
			case HSSFCell.CELL_TYPE_ERROR:
			case HSSFCell.CELL_TYPE_FORMULA:
				break;
			case HSSFCell.CELL_TYPE_NUMERIC:
				ret += (long) cell.getNumericCellValue();
				if (ret.indexOf(".") >= 0) {
					ret = ret.substring(0, ret.indexOf("."));
				}
				break;
			case HSSFCell.CELL_TYPE_STRING:
				ret = cell.getStringCellValue();
				break;
			}
		}
		return ret;
	}
}
